/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package generalCode;

import java.sql.*;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author kaka
 */
public class Connectionsql {

    private Connection conn;
    private String password;
    private String serverName;
    private String port;
    private String databaseName;
    private String userName;
    private static Connectionsql instance;

   public static Connectionsql newInstance() {
        if (instance == null) {
            instance = new Connectionsql();
        }
        return instance;
    }

    private Connectionsql() {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(Connectionsql.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public Connection getConn() throws SQLException {
        conn = DriverManager.getConnection("jdbc:sqlserver://" + serverName + ":" + port + ";databaseName=" + databaseName + ";user=" + userName + ";password=" + password);
        return conn;
    }

    public void closeConn() throws SQLException {
        if (conn != null) {
            conn.close();
        }
    }

    public boolean checkConnection(String serverName, String port, String databaseName, String userName, String pass) throws SQLException {
        this.userName = userName;
        this.databaseName = databaseName;
        this.password = pass;
        this.serverName = serverName;
        this.port = port;

        conn = DriverManager.getConnection("jdbc:sqlserver://" + serverName + ":" + port + ";databaseName=" + databaseName + ";user=" + userName + ";password=" + pass);
        return (conn != null) ? true : false;
    }

    public ResultSet query(String sql) throws SQLException {
        getConn();
        return conn.createStatement().executeQuery(sql);
    }

    public boolean update(String sql) throws SQLException {
        getConn();
        int rs = conn.createStatement().executeUpdate(sql);
        if (rs > 0) {
            return true;
        }
        return false;
    }

    public ResultSet queryWithParam(String sql, Object[] paramValue) throws SQLException {
        getConn();
        PreparedStatement preState = conn.prepareStatement(sql);
        for (int i = 0; i < paramValue.length; i++) {
            preState.setObject(i + 1, paramValue[i]);
        }
        return preState.executeQuery();
    }

    public boolean updateWithParam(String sql, Object[] paramValue) throws SQLException {
        getConn();
        PreparedStatement preState = conn.prepareStatement(sql);
        for (int i = 0; i < paramValue.length; i++) {
            preState.setObject(i + 1, paramValue[i]);
        }
        int rs = preState.executeUpdate();
        if (rs > 0) {
            return true;
        }
        return false;
    }

    public void callProcedure(String name, Object[] paramValue) throws SQLException {
        getConn();
        String sql = "{call " + name + "}";
        CallableStatement call = conn.prepareCall(sql);
        for (int i = 0; i < paramValue.length; i++) {
            call.setObject(i + 1, paramValue[i]);
        }

    }
}
